<?php
// +----------------------------------------------------------------------
// | Copyright © Sogu Technologies Co. Ltd 2018-2050 All rights reserved.
// +----------------------------------------------------------------------
// | File name: ChannelLoanBeforeDataController.php
// +----------------------------------------------------------------------
// | Author: cg
// +----------------------------------------------------------------------
// | Description:  渠道进件统计
// +----------------------------------------------------------------------
// | Others: 其他内容的说明
// +----------------------------------------------------------------------
// | History:
// +----------------------------------------------------------------------
// | 1.Date: 2019-03-12 Author: cg Modification: 创建
// +----------------------------------------------------------------------

namespace App\Http\Controllers\Backend\FollowData;


use App\Http\Controllers\Controller;
use App\Models\LoanApply;
use App\Models\SettingSource;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Illuminate\Http\Request;

class ChannelLoanBeforeDataController extends Controller
{

    public function __construct()
    {
        $this->middleware('auth:backend');
        $this->user = auth('backend')->user();
    }

    public $select_start_time;
    public $select_end_time;
    public $select_channel;
    public $flag = 'day';

    public function requestParams(Request $request)
    {
        $start_time = $request->get('start_time', '');
        $end_time = $request->get('end_time', '');
        $channel = $request->get('channel', '');
        $flag = $request->get('flag', 'day');
        $is_export = $request->get('is_export', 0);
        if(!$start_time || !$end_time){
            return failed('请选择开始结束时间');
        }
        $this->select_start_time = $start_time;
        $this->select_end_time = $end_time;
        $this->select_channel = $channel;
        $this->flag = $flag;
        $result = $this->getAllList();
        $data = $this->formatReturnData($result);
        if($is_export){
            $this->exportData($data);
        }
        return success(["list" => $data, 'total' => count($data)]);
    }

    /**
     * 格式化导出数据
     * @author cg
     * @date 2019-3-12 17:20
     * return array
     */
    private function formatReturnData($data)
    {
        $formatArrs = [];
        $datas = $this->channel_statistics_data();
        foreach ($datas as $k => $v){
            array_push($formatArrs, $v);
        }
        foreach ($formatArrs as $key => $item){
            $search_return = $this->searchChannelData($data, $item['channel']);
            $formatArrs[$key] = array_merge($search_return, $item);
        }
        return $formatArrs;
    }

    private function searchChannelData($data, $search_channel)
    {
        $return = array_flip(array_keys($data));
        foreach ($data as $field => $v){
            $num = array_search($search_channel, array_flip($this->strArrKeyAndValue($v)));
            if($num == false){
                $return[$field] = 0;
            }else{
                $return[$field] = $num;
            }
        }
        return $return;
    }
    private function strArrKeyAndValue($value){
        foreach ($value as $k => &$v){
            if(is_float($v)){
                $v = number_format($v, 2, '.', '');
            }
        }
        return $value;
    }
    private function getAllList()
    {
        $ret = [
            'register_num' => $this->channel_register_data(),
            'confirm_get_money_num' => $this->confirmGetMoneyNum(), // 点击确认要款数
            'apply_true_num' => $this->loanApplySuccessTrueNum(), // 放款数
            'apply_true_sum_money' => $this->loanApplaySuccessTrueSumMoney(), // 放款金额
        ];
        // 放款件均
        $avg = $this->applyMoneyAvg($ret['apply_true_num'], $ret['apply_true_sum_money']);
        $ret['apply_money_avg'] = $avg;
        return $ret;
    }

    /**
     * 每个渠道的放款平均量
     * @author cg
     * @date 2019-3-12 15:52
     * return array
     */
    private function applyMoneyAvg($numArr, $sumArr)
    {
        $returnArr = [];
        foreach ($numArr as $key=>$value){
            $returnArr[$key] = sprintf("%.2f", $sumArr[$key]/$value);
        }
        return $returnArr;
    }

    /**
     * 每个渠道的放款总金额
     * @author cg
     * @date 2019-3-12 15:52
     * return array
     */
    private function loanApplaySuccessTrueSumMoney()
    {
        $loan_apply = new LoanApply();
        $loan_apply->setConnection('backend');
        $loanApplys = $loan_apply->with('getUser')->select('amount', 'user_id')
            ->whereRaw('created_at >= ? and created_at <= ?', [$this->select_start_time, $this->select_end_time])
            ->where('loan_status', LoanApply::LOAN_STATUS_SUCCESS)
            ->get()->toArray();
        $loanApplyArr = [];
        foreach ($loanApplys as $key => $value){
            if($value['get_user']['source'] == ""){
                $loanApplyArr[0][] = $value['amount'];
            }else{
                $loanApplyArr[$value['get_user']['source']][] = $value['amount'];
            }
        }
        foreach ($loanApplyArr as $key=>$value){
            $loanApplyArr[$key] = round(array_sum($value)/10000,2);
        }
        return $loanApplyArr;
    }

    /**
     * 每个渠道的放款数
     * @author cg
     * @date 2019-3-12 15:30
     * return array
     */
    private function loanApplySuccessTrueNum()
    {
        $loan_apply = new LoanApply();
        $loan_apply->setConnection('backend');
        $loanApplys = $loan_apply::with('getUser')->select('user_id')
            ->whereRaw('created_at >= ? and created_at <= ?', [$this->select_start_time, $this->select_end_time])
            ->where('loan_status', LoanApply::LOAN_STATUS_SUCCESS)
            ->get()->toArray();
        $loanApplyArr = [];
        foreach ($loanApplys as $key => $value){
            if($value['get_user']['source'] == ""){
                $loanApplyArr[0][] = $value['user_id'];
            }else{
                $loanApplyArr[$value['get_user']['source']][] = $value['user_id'];
            }
        }
        foreach ($loanApplyArr as $key=>$value){
            $loanApplyArr[$key] = count($value);
        }
        return $loanApplyArr;
    }


    /**
     * 每个渠道的确认要款数
     * @author cg
     * @date 2019-3-12 14:30
     * return array
     */
    private function confirmGetMoneyNum()
    {
//        $loanApplys = LoanApply::leftJoin('users', 'users.id', '=', 'loan_apply.user_id')
//            ->select([
//                DB::raw('COUNT(loan_apply.created_at)'),
//                DB::raw("(CASE
//                    WHEN '{$this->flag}' = 'day' THEN
//                    DATE_FORMAT( loan_apply.created_at, '%Y-%m-%d' )
//                    WHEN '{$this->flag}' = 'hour' THEN
//                    DATE_FORMAT( loan_apply.created_at, '%Y-%m-%d %H' )
//                    WHEN '{$this->flag}' = 'month' THEN
//                    DATE_FORMAT( loan_apply.created_at, '%Y-%m' )
//                    END
//	            ) as flag"),
//                DB::raw('GROUP_CONCAT(DISTINCT users.source  ORDER BY source ) AS channel'),
//            ])
//            ->whereRaw('( loan_apply.created_at BETWEEN ? AND ? ',[$this->select_start_time, $this->select_end_time])
//            ->whereRaw('loan_apply.loan_status in (?,?,?) ) OR (
//            users.created_at BETWEEN ? AND ? ) ', [LoanApply::LOAN_STATUS_SUCCESS, LoanApply::LOAN_STATUS_FAIL,
//                LoanApply::LOAN_CHANGE_CARD, $this->select_start_time, $this->select_end_time])
//            ->groupBy('flag')
//            ->get();

//        dd($loanApplys->toArray());
        $loan_apply = new LoanApply();
        $loan_apply->setConnection('backend');
        $loanApplys = $loan_apply::with('getUser')->select('user_id')
            ->whereRaw('created_at >= ? and created_at <= ?', [$this->select_start_time, $this->select_end_time])
            ->whereIn('loan_status', [LoanApply::LOAN_STATUS_SUCCESS, LoanApply::LOAN_STATUS_FAIL, LoanApply::LOAN_CHANGE_CARD])
            ->get()->toArray();

        $loanApplyArr = [];
        foreach ($loanApplys as $key => $value){
            $loanApplyArr[$value['get_user']['source']][] = $value['user_id'];
        }
        foreach ($loanApplyArr as $key=>$value){
            $loanApplyArr[$key] = count($value);
        }
        return $loanApplyArr;
    }


    private function exportData($data){
        ob_clean();
        if (!$data) {
            return failed('暂无数据');
        }
        $header = [
            '统计开始时间', '统计结束时间', '渠道', '渠道名称', '注册数', '申请数', '申请金额(万元)', '风控通过', '风控拒绝', '资方通过', '通过金额', '审批通过率',
            '点击确认要款数', '放款数', '放款金额(万元)', '放款均件'
        ];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $i = 0;
        foreach ($header as $value) {
            $cellName = chr(65 + $i) . "1";
            $sheet->setCellValue($cellName, $value)->calculateColumnWidths();
            $sheet->getColumnDimension(chr(65 + $i))->setWidth('10');
            $i++;
        }
        $field = [
            'start_statistics_time','end_statistics_time', 'channel', 'channel_name','register_num', 'approval_num', 'approval_money_sum', 'risk_pass_num', 'risk_fail_num',
            'management_pass_sum','management_pass_money_sum','management_pass_rate','confirm_get_money_num',
            'apply_true_num','apply_true_sum_money','apply_money_avg'
        ];
        //设置value
        $len = count($field);
        foreach ($data as $key => $item) {
            $row = 2 + ($key * 2);
            for ($i = 0; $i < $len; $i++) {
                $sheet->setCellValueExplicit(chr(65 + $i) . $row, $item[$field[$i]], DataType::TYPE_STRING);
            }
        }
        $writer = new Xlsx($spread);
        //$file_name = $start_time . '~' . $end_time . '-' . UserFollow::USER_FOLLOW_FLAG[$flag];
        $file_name = '渠道进件表';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename="'.$file_name.'.xlsx"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        @ob_flush();
        exit;
    }

    /**
     *description: 渠道注册人数统计
     * @author liChunLong
     * @return array
     */
    private function channel_register_data() {
        //注册人数
        $start_time = $this->select_start_time;
        $end_time = $this->select_end_time;
        $sql = "SELECT `source` ,count(`source`) AS cur_source_register_num ";
        $sql .= " FROM `users` WHERE `created_at` BETWEEN '{$start_time}' AND '{$end_time}'";
        $sql .= "GROUP BY `source` ";
//        var_dump($sql);
        $register_data = DB::connection('backend')->select($sql);
        $reg_data = [];
        foreach ($register_data as $k=>$v) {
            if(is_null($v->source)) {
                continue;
            }
            $reg_data[$v->source] = $v->cur_source_register_num;
        }
        return $reg_data;
//        var_dump($register_data);
    }

    /**
     *description: 渠道进件统计
     * @data: 2019-03-12
     * @author liChunLong
     * @return array
     *
     */
    private function channel_statistics_data() {
        $start_time = $this->select_start_time;
        $end_time = $this->select_end_time;
        $channels_arr = (new SettingSource())->setConnection('backend')->pluck('source_name','source_id')->toArray();
        //申请人数
        $sql = "SELECT a.id ,a.user_id,a.loan_money,a.actually_loan_money,a.trans_id,b.source AS channel,a.status,a.current,a.risk_status,a.apply_time  ";
        $sql .= " FROM `user_loan_requests` as a";
        $sql .= " left join users as b on a.user_id = b.id";
        $sql .= " WHERE a.created_at BETWEEN '{$start_time}' AND '{$end_time}'";
        $sql .= " AND a.risk_status in(1,2,9)";
        if($this->select_channel){
            $sql .= " AND b.source in(".$this->select_channel.")";
        }
        $approval_data = DB::connection('backend')->select($sql);

        $channel_data = [];
        foreach ($approval_data as $v) {
            if($v->channel == ''){
                $v->channel = 0;
            }
            $channel_data[$v->channel][] = $v;
        }
//        var_dump($channel_data);
        $ret = [];
        foreach ($channel_data as $k0=>$v0) {
            $approval_num = count($v0); //申请数量
//            var_dump('申请数量 =='.$approval_num);
            $approval_money_sum = 0;//申请金额
            $risk_pass_num = 0;//风控通过数量
            $risk_pass_money_sum = 0;//风控通过金额
            $risk_fail_num = 0;//风控失败数量
            $risk_other_num = 0;//风控处理中数量
            $management_pass_sum = 0;//资方通过数
            $management_pass_money_sum = 0;//资方通过金额
            $management_pass_rate = 0;//资方通过率

            foreach ($v0 as $k=>$v) {
                $approval_money_sum += $v->loan_money;
                //risk_status 风控状态 0=初始状态 1=通过 2=拒绝 3=继续 4=处理中
                if ($v->risk_status == 1) {
                    $risk_pass_num +=1;
                    $risk_pass_money_sum += $v->loan_money;
                }
                if ($v->risk_status == 2) {
                    $risk_fail_num += 1;
                }
                if ($v->risk_status == 9) {
                    $risk_other_num += 1;
                }

                //status 资方状态 ：0未完成 1待申请 2申请中 3申请成功 4申请失败 5超时 6完全超时',
                if ($v->status == 3) {
                    $management_pass_sum += 1;
                    $management_pass_money_sum += $v->actually_loan_money;
                }
            }
//            var_dump('management_pass_sum =='.$management_pass_sum);
            $management_pass_rate == 0;
            if ($approval_num > 0) {
                $management_pass_rate = sprintf('%.2f',$management_pass_sum/$approval_num * 100) . "%";
            }
            $approval_money_sum = sprintf('%.2f',$approval_money_sum/10000/100);//单位从分到万元
            $risk_pass_money_sum = sprintf('%.2f',$risk_pass_money_sum/10000/100);//单位从分到万元
            $management_pass_money_sum = sprintf('%.2f',$management_pass_money_sum/10000/100);//单位从分到万元

//            var_dump('risk_pass_num =='.$risk_pass_num);
//            var_dump('risk_fail_num =='.$risk_fail_num);
//            var_dump('risk_other_num =='.$risk_other_num);
//            var_dump('approval_money_sum =='.$approval_money_sum);
//            var_dump('risk_pass_money_sum =='.$risk_pass_money_sum);
//            var_dump('management_pass_rate =='.$management_pass_rate);
            $ret[$k0]['approval_num']= $approval_num;
            $ret[$k0]['risk_pass_num']= $risk_pass_num;
            $ret[$k0]['risk_fail_num']= $risk_fail_num;
            $ret[$k0]['approval_money_sum']= $approval_money_sum;
            $ret[$k0]['risk_pass_money_sum']= $risk_pass_money_sum;
            $ret[$k0]['management_pass_sum']= $management_pass_sum;
            $ret[$k0]['management_pass_money_sum']= $management_pass_money_sum;
            $ret[$k0]['management_pass_rate']= $management_pass_rate;
            $ret[$k0]['risk_other_num']= $risk_other_num;
            $ret[$k0]['channel']= $k0;
            $ret[$k0]['start_statistics_time'] = $start_time;
            $ret[$k0]['end_statistics_time']= $end_time;
            if ($k0 == 0 || empty($k0)) {
                $ret[$k0]['channel_name']= 'APP';
            }else {
                if (isset($channels_arr[$k0]) && !empty($channels_arr[$k0])) {
                    $ret[$k0]['channel_name'] = $channels_arr[$k0];
                }else {
                    $ret[$k0]['channel_name']= 'error channel';
                }
            }

        }
//        var_dump($ret);
//        die;
        return $ret;
    }



}